{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# 分省\n",
    "#江苏\n",
    "all = data.loc[data['ACCEPT_CITY_NAME'].isin(['无锡市','淮安市'])].copy()\n",
    "# all.sort_values(['ACCOUNT_ID'])\n",
    "# 转换时间\n",
    "all['REPAY_TIME1']=pd.to_datetime(all.REPAY_TIME1,format='%Y-%m-%d')\n",
    "all['REPAY_TIME2']=pd.to_datetime(all.REPAY_TIME2,format='%Y-%m-%d')\n",
    "all['REPAY_TIME3']=pd.to_datetime(all.REPAY_TIME3,format='%Y-%m-%d')\n",
    "all['REQUEST_DATE']=pd.to_datetime(all.REQUEST_DATE,format='%Y-%m-%d').dt.month\n",
    "# all[all['REQUEST_DATE']==5]\n",
    "\n",
    "# 按完成时间分类\n",
    "m3 = all[all['REQUEST_DATE']==3]\n",
    "m4 = all[all['REQUEST_DATE']==4]\n",
    "m5 = all[all['REQUEST_DATE']==5]\n",
    "#4月\n",
    "#按mob分类\n",
    "#3月完成\n",
    "list_m3_t_4 = list(m3[m3['STATUS1']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m3_m1_4 = list(m3[m3['REPAY_TIME1']>='2019-05-01']['ACCOUNT_ID'])+list(m3[m3['STATUS1']=='OPEN']['ACCOUNT_ID'])\n",
    "list_m3_c_4 = list(set(m3['ACCOUNT_ID']) ^ set(list_m3_t_4 + list_m3_m1_4))\n",
    "\n",
    "list_all_t_4 = list_m3_t_4\n",
    "list_all_m1_4 = list_m3_m1_4\n",
    "list_all_c_4 = list_m3_c_4 + list(m4['ACCOUNT_ID'])\n",
    "#5月\n",
    "#按mob分类\n",
    "#3月完成\n",
    "list_m3_t_5  = list(m3[m3['STATUS2']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m3_m1_5 = list(m3[(m3['STATUS2']=='OPEN') & (m3['STATUS1']!='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME2']>='2019-06-01') & (m3['REPAY_TIME1']<'2019-06-01')]['ACCOUNT_ID'])\n",
    "list_m3_m2_5 = list(m3[(m3['STATUS2']=='OPEN') & (m3['STATUS1']=='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME2']>='2019-06-01') & (m3['REPAY_TIME1']>='2019-06-01')]['ACCOUNT_ID'])\n",
    "list_m3_c_5  = list(set(m3['ACCOUNT_ID']) ^ set(list_m3_t_5 + list_m3_m1_5 + list_m3_m2_5))\n",
    "\n",
    "\n",
    "#4月完成\n",
    "list_m4_t_5 = list(m4[m4['STATUS2']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m4_m1_5 = list(m4[m4['STATUS2']=='OPEN']['ACCOUNT_ID']) + list(m4[m4['REPAY_TIME2']>='2019-06-01']['ACCOUNT_ID'])\n",
    "list_m4_c_5  = list(set(m4['ACCOUNT_ID']) ^ set(list_m4_t_5 + list_m4_m1_5))\n",
    "\n",
    "#总的\n",
    "list_all_t_5  = list_m3_t_5 + list_m4_t_5\n",
    "list_all_m1_5 = list_m3_m1_5 +list_m4_m1_5\n",
    "list_all_m2_5 = list_m3_m2_5\n",
    "list_all_c_5  = list_m3_c_5 + list_m4_c_5 + list(m5['ACCOUNT_ID'])\n",
    "\n",
    "#6月\n",
    "#按mob分类\n",
    "#3月完成\n",
    "list_m3_t_6  = list(m3[m3['STATUS3']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m3_m1_6 = list(m3[(m3['STATUS3']=='OPEN') & (m3['STATUS2']!='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME3']>='2019-07-01') & (m3['REPAY_TIME2']<'2019-07-01')]['ACCOUNT_ID'])\n",
    "list_m3_m2_6 = list(m3[(m3['STATUS3']=='OPEN') & (m3['STATUS2']=='OPEN') & (m3['STATUS1']!='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME3']>='2019-07-01') & (m3['REPAY_TIME2']>='2019-07-01') & (m3['REPAY_TIME1']<'2019-07-01')]['ACCOUNT_ID'])\n",
    "list_m3_m3_6 = list(m3[(m3['STATUS3']=='OPEN') & (m3['STATUS2']=='OPEN') & (m3['STATUS1']=='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME3']>='2019-07-01') & (m3['REPAY_TIME2']>='2019-07-01') & (m3['REPAY_TIME1']>='2019-07-01')]['ACCOUNT_ID'])\n",
    "list_m3_c_6  = list(set(m3['ACCOUNT_ID']) ^ set(list_m3_t_6 + list_m3_m1_6 + list_m3_m2_6 + list_m3_m3_6))\n",
    "\n",
    "#4月完成\n",
    "list_m4_t_6 = list(m4[m4['STATUS3']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m4_m1_6 = list(m4[(m4['STATUS3']=='OPEN') & (m4['STATUS2']!='OPEN')]['ACCOUNT_ID']) + list(m4[(m4['REPAY_TIME3']>='2019-07-01') & (m4['REPAY_TIME2']<'2019-07-01')]['ACCOUNT_ID'])\n",
    "list_m4_m2_6 = list(m4[(m4['STATUS3']=='OPEN') & (m4['STATUS2']=='OPEN')]['ACCOUNT_ID']) + list(m4[(m4['REPAY_TIME3']>='2019-07-01') & (m4['REPAY_TIME2']>='2019-07-01')]['ACCOUNT_ID'])\n",
    "list_m4_c_6  = list(set(m4['ACCOUNT_ID']) ^ set(list_m4_t_6 + list_m4_m1_6 + list_m4_m2_6))\n",
    "# 5月\n",
    "list_m5_t_6  = list(m5[m5['STATUS3']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m5_m1_6 = list(m5[m5['STATUS3']=='OPEN']['ACCOUNT_ID']) + list(m5[m5['REPAY_TIME3']>='2019-07-01']['ACCOUNT_ID'])\n",
    "list_m5_c_6  = list(set(m5['ACCOUNT_ID']) ^ set(list_m5_t_6 + list_m5_m1_6))\n",
    "\n",
    "#总的\n",
    "list_all_t_6  = list_m3_t_6 + list_m4_t_6 + list_m5_t_6\n",
    "list_all_m1_6 = list_m3_m1_6 + list_m4_m1_6 + list_m5_m1_6\n",
    "list_all_m2_6 = list_m3_m2_6 + list_m4_m2_6\n",
    "list_all_m3_6 = list_m3_m3_6\n",
    "list_all_c_6  = list_m3_c_6 + list_m4_c_6 + list_m5_c_6 + list(m5['ACCOUNT_ID'])\n",
    "len(list_all_c_6)\n",
    "\n",
    "# 计算结果存到dataframe\n",
    "# C-M1\n",
    "c_m1_04 = round(len(list_all_m1_4) / m3.shape[0],6)\n",
    "c_m1_05 = round(len(list_all_m1_5) / len(list_all_c_4),6)\n",
    "c_m1_06 = round(len(list_all_m1_6) / len(list_all_c_5),6)\n",
    "# M1-M2\n",
    "m1_m2_05 = round(len(list_all_m2_5) / len(list_all_m1_4),6)\n",
    "m1_m2_06 = round(len(list_all_m2_6) / len(list_all_m1_5),6)\n",
    "# M2-M3\n",
    "m2_m3_06 = round(len(list_all_m3_6) / (len(list_all_m2_5)+0.001),6)\n",
    "\n",
    "#C-C\n",
    "c_c_04 = round(len(set(list_all_c_4)&set(m3['ACCOUNT_ID']))/len(set(m3['ACCOUNT_ID'])),6)\n",
    "c_c_05 = round(len(set(list_all_c_4)&set(list_all_c_5))/len(set(list_all_c_4)),6)\n",
    "c_c_06 = round(len(set(list_all_c_6)&set(list_all_c_5))/len(set(list_all_c_5)),6)\n",
    "#M1-C\n",
    "m1_c1_05 = round(len(set(list_all_m1_4)&set(list_all_c_5))/len(set(list_all_m1_4)),6)\n",
    "m1_c1_06 = round(len(set(list_all_m1_5)&set(list_all_c_6))/len(set(list_all_m1_5)),6)\n",
    "#M2-C\n",
    "m2_c_06 = round(len(set(list_all_m2_5)&set(list_all_c_6))/(len(list_all_m2_5)+0.001),6)\n",
    "#M1-M1\n",
    "m1_m1_05 = round(len(set(list_all_m1_5)&set(list_all_m1_4))/len(set(list_all_m1_4)),6)\n",
    "m1_m1_06 = round(len(set(list_all_m1_5)&set(list_all_m1_6))/len(set(list_all_m1_5)),6)\n",
    "#M2-M1\n",
    "m2_m1_06 = round(len(set(list_all_m2_5)&set(list_all_m1_6))/(len(set(list_all_m2_5))+0.001),6)\n",
    "\n",
    "# 导出数据\n",
    "date = {\n",
    "    \"billingmonth\":[\"C-M1\",\"M1-M2\",\"M2-M3\",\"C-C\",\"M1-C\",\"M2-C\",\"M1-M1\",\"M2-M1\"],\n",
    "    \"201904\"      :[c_m1_04,\"\",\"\",c_c_04,\"\",\"\",\"\",\"\"],\n",
    "    \"201905\"      :[c_m1_05,m1_m2_05,\"\",c_c_05,m1_c1_05,\"\",m1_m1_05,\"\"],\n",
    "    \"201906\"      :[c_m1_06,m1_m2_06,m2_m3_06,c_c_06,m1_c1_06,m2_c_06,m1_m1_06,m2_m1_06]\n",
    "}\n",
    "df=pd.DataFrame(date)\n",
    "df.to_csv('迁移率分省—江苏.csv')\n",
    "\n",
    "#广东\n",
    "all = data.loc[data['ACCEPT_CITY_NAME'].isin(['中山市','东莞市','惠州市'])].copy()\n",
    "# all.sort_values(['ACCOUNT_ID'])\n",
    "# 转换时间\n",
    "all['REPAY_TIME1']=pd.to_datetime(all.REPAY_TIME1,format='%Y-%m-%d')\n",
    "all['REPAY_TIME2']=pd.to_datetime(all.REPAY_TIME2,format='%Y-%m-%d')\n",
    "all['REPAY_TIME3']=pd.to_datetime(all.REPAY_TIME3,format='%Y-%m-%d')\n",
    "all['REQUEST_DATE']=pd.to_datetime(all.REQUEST_DATE,format='%Y-%m-%d').dt.month\n",
    "# all[all['REQUEST_DATE']==5]\n",
    "\n",
    "# 按完成时间分类\n",
    "m3 = all[all['REQUEST_DATE']==3]\n",
    "m4 = all[all['REQUEST_DATE']==4]\n",
    "m5 = all[all['REQUEST_DATE']==5]\n",
    "#4月\n",
    "#按mob分类\n",
    "#3月完成\n",
    "list_m3_t_4 = list(m3[m3['STATUS1']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m3_m1_4 = list(m3[m3['REPAY_TIME1']>='2019-05-01']['ACCOUNT_ID'])+list(m3[m3['STATUS1']=='OPEN']['ACCOUNT_ID'])\n",
    "list_m3_c_4 = list(set(m3['ACCOUNT_ID']) ^ set(list_m3_t_4 + list_m3_m1_4))\n",
    "\n",
    "list_all_t_4 = list_m3_t_4\n",
    "list_all_m1_4 = list_m3_m1_4\n",
    "list_all_c_4 = list_m3_c_4 + list(m4['ACCOUNT_ID'])\n",
    "#5月\n",
    "#按mob分类\n",
    "#3月完成\n",
    "list_m3_t_5  = list(m3[m3['STATUS2']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m3_m1_5 = list(m3[(m3['STATUS2']=='OPEN') & (m3['STATUS1']!='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME2']>='2019-06-01') & (m3['REPAY_TIME1']<'2019-06-01')]['ACCOUNT_ID'])\n",
    "list_m3_m2_5 = list(m3[(m3['STATUS2']=='OPEN') & (m3['STATUS1']=='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME2']>='2019-06-01') & (m3['REPAY_TIME1']>='2019-06-01')]['ACCOUNT_ID'])\n",
    "list_m3_c_5  = list(set(m3['ACCOUNT_ID']) ^ set(list_m3_t_5 + list_m3_m1_5 + list_m3_m2_5))\n",
    "\n",
    "\n",
    "#4月完成\n",
    "list_m4_t_5 = list(m4[m4['STATUS2']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m4_m1_5 = list(m4[m4['STATUS2']=='OPEN']['ACCOUNT_ID']) + list(m4[m4['REPAY_TIME2']>='2019-06-01']['ACCOUNT_ID'])\n",
    "list_m4_c_5  = list(set(m4['ACCOUNT_ID']) ^ set(list_m4_t_5 + list_m4_m1_5))\n",
    "\n",
    "#总的\n",
    "list_all_t_5  = list_m3_t_5 + list_m4_t_5\n",
    "list_all_m1_5 = list_m3_m1_5 +list_m4_m1_5\n",
    "list_all_m2_5 = list_m3_m2_5\n",
    "list_all_c_5  = list_m3_c_5 + list_m4_c_5 + list(m5['ACCOUNT_ID'])\n",
    "\n",
    "#6月\n",
    "#按mob分类\n",
    "#3月完成\n",
    "list_m3_t_6  = list(m3[m3['STATUS3']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m3_m1_6 = list(m3[(m3['STATUS3']=='OPEN') & (m3['STATUS2']!='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME3']>='2019-07-01') & (m3['REPAY_TIME2']<'2019-07-01')]['ACCOUNT_ID'])\n",
    "list_m3_m2_6 = list(m3[(m3['STATUS3']=='OPEN') & (m3['STATUS2']=='OPEN') & (m3['STATUS1']!='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME3']>='2019-07-01') & (m3['REPAY_TIME2']>='2019-07-01') & (m3['REPAY_TIME1']<'2019-07-01')]['ACCOUNT_ID'])\n",
    "list_m3_m3_6 = list(m3[(m3['STATUS3']=='OPEN') & (m3['STATUS2']=='OPEN') & (m3['STATUS1']=='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME3']>='2019-07-01') & (m3['REPAY_TIME2']>='2019-07-01') & (m3['REPAY_TIME1']>='2019-07-01')]['ACCOUNT_ID'])\n",
    "list_m3_c_6  = list(set(m3['ACCOUNT_ID']) ^ set(list_m3_t_6 + list_m3_m1_6 + list_m3_m2_6 + list_m3_m3_6))\n",
    "\n",
    "#4月完成\n",
    "list_m4_t_6 = list(m4[m4['STATUS3']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m4_m1_6 = list(m4[(m4['STATUS3']=='OPEN') & (m4['STATUS2']!='OPEN')]['ACCOUNT_ID']) + list(m4[(m4['REPAY_TIME3']>='2019-07-01') & (m4['REPAY_TIME2']<'2019-07-01')]['ACCOUNT_ID'])\n",
    "list_m4_m2_6 = list(m4[(m4['STATUS3']=='OPEN') & (m4['STATUS2']=='OPEN')]['ACCOUNT_ID']) + list(m4[(m4['REPAY_TIME3']>='2019-07-01') & (m4['REPAY_TIME2']>='2019-07-01')]['ACCOUNT_ID'])\n",
    "list_m4_c_6  = list(set(m4['ACCOUNT_ID']) ^ set(list_m4_t_6 + list_m4_m1_6 + list_m4_m2_6))\n",
    "# 5月\n",
    "list_m5_t_6  = list(m5[m5['STATUS3']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m5_m1_6 = list(m5[m5['STATUS3']=='OPEN']['ACCOUNT_ID']) + list(m5[m5['REPAY_TIME3']>='2019-07-01']['ACCOUNT_ID'])\n",
    "list_m5_c_6  = list(set(m5['ACCOUNT_ID']) ^ set(list_m5_t_6 + list_m5_m1_6))\n",
    "\n",
    "#总的\n",
    "list_all_t_6  = list_m3_t_6 + list_m4_t_6 + list_m5_t_6\n",
    "list_all_m1_6 = list_m3_m1_6 + list_m4_m1_6 + list_m5_m1_6\n",
    "list_all_m2_6 = list_m3_m2_6 + list_m4_m2_6\n",
    "list_all_m3_6 = list_m3_m3_6\n",
    "list_all_c_6  = list_m3_c_6 + list_m4_c_6 + list_m5_c_6 + list(m5['ACCOUNT_ID'])\n",
    "len(list_all_c_6)\n",
    "\n",
    "# 计算结果存到dataframe\n",
    "# C-M1\n",
    "c_m1_04 = round(len(list_all_m1_4) / (m3.shape[0]+0.001),6)\n",
    "c_m1_05 = round(len(list_all_m1_5) / len(list_all_c_4),6)\n",
    "c_m1_06 = round(len(list_all_m1_6) / len(list_all_c_5),6)\n",
    "# M1-M2\n",
    "m1_m2_05 = round(len(list_all_m2_5) / (len(list_all_m1_4)+0.001),6)\n",
    "m1_m2_06 = round(len(list_all_m2_6) / len(list_all_m1_5),6)\n",
    "# M2-M3\n",
    "m2_m3_06 = round(len(list_all_m3_6) / (len(list_all_m2_5)+0.001),6)\n",
    "\n",
    "#C-C\n",
    "c_c_04 = round(len(set(list_all_c_4)&set(m3['ACCOUNT_ID']))/(len(set(m3['ACCOUNT_ID']))+0.001),6)\n",
    "c_c_05 = round(len(set(list_all_c_4)&set(list_all_c_5))/len(set(list_all_c_4)),6)\n",
    "c_c_06 = round(len(set(list_all_c_6)&set(list_all_c_5))/len(set(list_all_c_5)),6)\n",
    "#M1-C\n",
    "m1_c1_05 = round(len(set(list_all_m1_4)&set(list_all_c_5))/(len(set(list_all_m1_4))+0.001),6)\n",
    "m1_c1_06 = round(len(set(list_all_m1_5)&set(list_all_c_6))/len(set(list_all_m1_5)),6)\n",
    "#M2-C\n",
    "m2_c_06 = round(len(set(list_all_m2_5)&set(list_all_c_6))/(len(list_all_m2_5)+0.001),6)\n",
    "#M1-M1\n",
    "m1_m1_05 = round(len(set(list_all_m1_5)&set(list_all_m1_4))/(len(set(list_all_m1_4))+0.001),6)\n",
    "m1_m1_06 = round(len(set(list_all_m1_5)&set(list_all_m1_6))/len(set(list_all_m1_5)),6)\n",
    "#M2-M1\n",
    "m2_m1_06 = round(len(set(list_all_m2_5)&set(list_all_m1_6))/(len(set(list_all_m2_5))+0.001),6)\n",
    "\n",
    "# 导出数据\n",
    "date = {\n",
    "    \"billingmonth\":[\"C-M1\",\"M1-M2\",\"M2-M3\",\"C-C\",\"M1-C\",\"M2-C\",\"M1-M1\",\"M2-M1\"],\n",
    "    \"201904\"      :[c_m1_04,\"\",\"\",c_c_04,\"\",\"\",\"\",\"\"],\n",
    "    \"201905\"      :[c_m1_05,m1_m2_05,\"\",c_c_05,m1_c1_05,\"\",m1_m1_05,\"\"],\n",
    "    \"201906\"      :[c_m1_06,m1_m2_06,m2_m3_06,c_c_06,m1_c1_06,m2_c_06,m1_m1_06,m2_m1_06]\n",
    "}\n",
    "df=pd.DataFrame(date)\n",
    "df.to_csv('迁移率分省—广东.csv')\n",
    "\n",
    "#重庆\n",
    "all = data.loc[data['ACCEPT_CITY_NAME'].isin(['重庆市辖区'])].copy()\n",
    "# all.sort_values(['ACCOUNT_ID'])\n",
    "# 转换时间\n",
    "all['REPAY_TIME1']=pd.to_datetime(all.REPAY_TIME1,format='%Y-%m-%d')\n",
    "all['REPAY_TIME2']=pd.to_datetime(all.REPAY_TIME2,format='%Y-%m-%d')\n",
    "all['REPAY_TIME3']=pd.to_datetime(all.REPAY_TIME3,format='%Y-%m-%d')\n",
    "all['REQUEST_DATE']=pd.to_datetime(all.REQUEST_DATE,format='%Y-%m-%d').dt.month\n",
    "# all[all['REQUEST_DATE']==5]\n",
    "\n",
    "# 按完成时间分类\n",
    "m3 = all[all['REQUEST_DATE']==3]\n",
    "m4 = all[all['REQUEST_DATE']==4]\n",
    "m5 = all[all['REQUEST_DATE']==5]\n",
    "#4月\n",
    "#按mob分类\n",
    "#3月完成\n",
    "list_m3_t_4 = list(m3[m3['STATUS1']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m3_m1_4 = list(m3[m3['REPAY_TIME1']>='2019-05-01']['ACCOUNT_ID'])+list(m3[m3['STATUS1']=='OPEN']['ACCOUNT_ID'])\n",
    "list_m3_c_4 = list(set(m3['ACCOUNT_ID']) ^ set(list_m3_t_4 + list_m3_m1_4))\n",
    "\n",
    "list_all_t_4 = list_m3_t_4\n",
    "list_all_m1_4 = list_m3_m1_4\n",
    "list_all_c_4 = list_m3_c_4 + list(m4['ACCOUNT_ID'])\n",
    "#5月\n",
    "#按mob分类\n",
    "#3月完成\n",
    "list_m3_t_5  = list(m3[m3['STATUS2']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m3_m1_5 = list(m3[(m3['STATUS2']=='OPEN') & (m3['STATUS1']!='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME2']>='2019-06-01') & (m3['REPAY_TIME1']<'2019-06-01')]['ACCOUNT_ID'])\n",
    "list_m3_m2_5 = list(m3[(m3['STATUS2']=='OPEN') & (m3['STATUS1']=='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME2']>='2019-06-01') & (m3['REPAY_TIME1']>='2019-06-01')]['ACCOUNT_ID'])\n",
    "list_m3_c_5  = list(set(m3['ACCOUNT_ID']) ^ set(list_m3_t_5 + list_m3_m1_5 + list_m3_m2_5))\n",
    "\n",
    "\n",
    "#4月完成\n",
    "list_m4_t_5 = list(m4[m4['STATUS2']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m4_m1_5 = list(m4[m4['STATUS2']=='OPEN']['ACCOUNT_ID']) + list(m4[m4['REPAY_TIME2']>='2019-06-01']['ACCOUNT_ID'])\n",
    "list_m4_c_5  = list(set(m4['ACCOUNT_ID']) ^ set(list_m4_t_5 + list_m4_m1_5))\n",
    "\n",
    "#总的\n",
    "list_all_t_5  = list_m3_t_5 + list_m4_t_5\n",
    "list_all_m1_5 = list_m3_m1_5 +list_m4_m1_5\n",
    "list_all_m2_5 = list_m3_m2_5\n",
    "list_all_c_5  = list_m3_c_5 + list_m4_c_5 + list(m5['ACCOUNT_ID'])\n",
    "\n",
    "#6月\n",
    "#按mob分类\n",
    "#3月完成\n",
    "list_m3_t_6  = list(m3[m3['STATUS3']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m3_m1_6 = list(m3[(m3['STATUS3']=='OPEN') & (m3['STATUS2']!='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME3']>='2019-07-01') & (m3['REPAY_TIME2']<'2019-07-01')]['ACCOUNT_ID'])\n",
    "list_m3_m2_6 = list(m3[(m3['STATUS3']=='OPEN') & (m3['STATUS2']=='OPEN') & (m3['STATUS1']!='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME3']>='2019-07-01') & (m3['REPAY_TIME2']>='2019-07-01') & (m3['REPAY_TIME1']<'2019-07-01')]['ACCOUNT_ID'])\n",
    "list_m3_m3_6 = list(m3[(m3['STATUS3']=='OPEN') & (m3['STATUS2']=='OPEN') & (m3['STATUS1']=='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME3']>='2019-07-01') & (m3['REPAY_TIME2']>='2019-07-01') & (m3['REPAY_TIME1']>='2019-07-01')]['ACCOUNT_ID'])\n",
    "list_m3_c_6  = list(set(m3['ACCOUNT_ID']) ^ set(list_m3_t_6 + list_m3_m1_6 + list_m3_m2_6 + list_m3_m3_6))\n",
    "\n",
    "#4月完成\n",
    "list_m4_t_6 = list(m4[m4['STATUS3']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m4_m1_6 = list(m4[(m4['STATUS3']=='OPEN') & (m4['STATUS2']!='OPEN')]['ACCOUNT_ID']) + list(m4[(m4['REPAY_TIME3']>='2019-07-01') & (m4['REPAY_TIME2']<'2019-07-01')]['ACCOUNT_ID'])\n",
    "list_m4_m2_6 = list(m4[(m4['STATUS3']=='OPEN') & (m4['STATUS2']=='OPEN')]['ACCOUNT_ID']) + list(m4[(m4['REPAY_TIME3']>='2019-07-01') & (m4['REPAY_TIME2']>='2019-07-01')]['ACCOUNT_ID'])\n",
    "list_m4_c_6  = list(set(m4['ACCOUNT_ID']) ^ set(list_m4_t_6 + list_m4_m1_6 + list_m4_m2_6))\n",
    "# 5月\n",
    "list_m5_t_6  = list(m5[m5['STATUS3']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m5_m1_6 = list(m5[m5['STATUS3']=='OPEN']['ACCOUNT_ID']) + list(m5[m5['REPAY_TIME3']>='2019-07-01']['ACCOUNT_ID'])\n",
    "list_m5_c_6  = list(set(m5['ACCOUNT_ID']) ^ set(list_m5_t_6 + list_m5_m1_6))\n",
    "\n",
    "#总的\n",
    "list_all_t_6  = list_m3_t_6 + list_m4_t_6 + list_m5_t_6\n",
    "list_all_m1_6 = list_m3_m1_6 + list_m4_m1_6 + list_m5_m1_6\n",
    "list_all_m2_6 = list_m3_m2_6 + list_m4_m2_6\n",
    "list_all_m3_6 = list_m3_m3_6\n",
    "list_all_c_6  = list_m3_c_6 + list_m4_c_6 + list_m5_c_6 + list(m5['ACCOUNT_ID'])\n",
    "len(list_all_c_6)\n",
    "\n",
    "# 计算结果存到dataframe\n",
    "# C-M1\n",
    "c_m1_04_z = len(list_all_m1_4)\n",
    "c_m1_04_m = / (m3.shape[0]+0.001),6)\n",
    "c_m1_05 = round(len(list_all_m1_5) / len(list_all_c_4),6)\n",
    "c_m1_06 = round(len(list_all_m1_6) / len(list_all_c_5),6)\n",
    "c_m1_07 = \n",
    "# M1-M2\n",
    "m1_m2_05 = round(len(list_all_m2_5) / (len(list_all_m1_4)+0.001),6)\n",
    "m1_m2_06 = round(len(list_all_m2_6) / len(list_all_m1_5),6)\n",
    "# M2-M3\n",
    "m2_m3_06 = round(len(list_all_m3_6) / (len(list_all_m2_5)+0.001),6)\n",
    "\n",
    "#C-C\n",
    "c_c_04 = round(len(set(list_all_c_4)&set(m3['ACCOUNT_ID']))/(len(set(m3['ACCOUNT_ID']))+0.001),6)\n",
    "c_c_05 = round(len(set(list_all_c_4)&set(list_all_c_5))/len(set(list_all_c_4)),6)\n",
    "c_c_06 = round(len(set(list_all_c_6)&set(list_all_c_5))/len(set(list_all_c_5)),6)\n",
    "#M1-C\n",
    "m1_c1_05 = round(len(set(list_all_m1_4)&set(list_all_c_5))/(len(set(list_all_m1_4))+0.001),6)\n",
    "m1_c1_06 = round(len(set(list_all_m1_5)&set(list_all_c_6))/len(set(list_all_m1_5)),6)\n",
    "#M2-C\n",
    "m2_c_06 = round(len(set(list_all_m2_5)&set(list_all_c_6))/(len(list_all_m2_5)+0.001),6)\n",
    "#M1-M1\n",
    "m1_m1_05 = round(len(set(list_all_m1_5)&set(list_all_m1_4))/(len(set(list_all_m1_4))+0.001),6)\n",
    "m1_m1_06 = round(len(set(list_all_m1_5)&set(list_all_m1_6))/len(set(list_all_m1_5)),6)\n",
    "#M2-M1\n",
    "m2_m1_06 = round(len(set(list_all_m2_5)&set(list_all_m1_6))/(len(set(list_all_m2_5))+0.001),6)\n",
    "\n",
    "# 导出数据\n",
    "date = {\n",
    "    \"billingmonth\":[\"C-M1\",\"M1-M2\",\"M2-M3\",\"C-C\",\"M1-C\",\"M2-C\",\"M1-M1\",\"M2-M1\"],\n",
    "    \"201904\"      :[c_m1_04,\"\",\"\",c_c_04,\"\",\"\",\"\",\"\"],\n",
    "    \"201905\"      :[c_m1_05,m1_m2_05,\"\",c_c_05,m1_c1_05,\"\",m1_m1_05,\"\"],\n",
    "    \"201906\"      :[c_m1_06,m1_m2_06,m2_m3_06,c_c_06,m1_c1_06,m2_c_06,m1_m1_06,m2_m1_06]\n",
    "}\n",
    "df=pd.DataFrame(date)\n",
    "df.to_csv('迁移率分省—重庆.csv')\n",
    "print(\"SUCCESS！\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-11T09:50:30.497029Z",
     "start_time": "2019-07-11T09:50:30.468121Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>合作方</th>\n",
       "      <th>用户类型</th>\n",
       "      <th>省份</th>\n",
       "      <th>账期</th>\n",
       "      <th>迁移状态</th>\n",
       "      <th>分子</th>\n",
       "      <th>分母</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>C-M1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>M1-M2</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>M2-M3</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>C-C</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>M1-C</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>M2-C</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>M1-M1</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>M2-M1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     合作方 用户类型 省份  账期  迁移状态  分子  分母\n",
       "0   C-M1    1  1   1     1   1   1\n",
       "1  M1-M2       1   1     1   1   1\n",
       "2  M2-M3           1     1   1   1\n",
       "3    C-C    1  1   1     1   1   1\n",
       "4   M1-C       1   1     1   1   1\n",
       "5   M2-C           1     1   1   1\n",
       "6  M1-M1       1   1     1   1   1\n",
       "7  M2-M1           1     1   1   1"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "date = {\n",
    "    \"合作方\"    :[\"C-M1\",\"M1-M2\",\"M2-M3\",\"C-C\",\"M1-C\",\"M2-C\",\"M1-M1\",\"M2-M1\"],\n",
    "    \"用户类型\"  :[1,\"\",\"\",1,\"\",\"\",\"\",\"\"],\n",
    "    \"省份\"      :[1,1,\"\",1,1,\"\",1,\"\"],\n",
    "    \"账期\"      :[\"201904\",\"201904\",\"201904\",\"201904\",1,1,1,1],\n",
    "    \"迁移状态\"  :[\"C-M1\",\"C-C\",1,1,1,1,1,1],\n",
    "    \"分子\"  :[1,1,1,1,1,1,1,1],\n",
    "    \"分母\"  :[1,1,1,1,1,1,1,1],\n",
    "}\n",
    "df=pd.DataFrame(date)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 总的\n",
    "all = data.loc[data['ACCEPT_CITY_NAME'].isin(['无锡市','淮安市','中山市','东莞市','惠州市','重庆市辖区'])].copy()\n",
    "# all.sort_values(['ACCOUNT_ID'])\n",
    "# 转换时间\n",
    "all['REPAY_TIME1']=pd.to_datetime(all.REPAY_TIME1,format='%Y-%m-%d')\n",
    "all['REPAY_TIME2']=pd.to_datetime(all.REPAY_TIME2,format='%Y-%m-%d')\n",
    "all['REPAY_TIME3']=pd.to_datetime(all.REPAY_TIME3,format='%Y-%m-%d')\n",
    "all['COMPLETE_TIME']=pd.to_datetime(all.COMPLETE_TIME,format='%Y-%m-%d').dt.month\n",
    "# all[all['REQUEST_DATE']==5]\n",
    "\n",
    "# 按完成时间分类\n",
    "m3 = all[all['COMPLETE_TIME']==3]\n",
    "m4 = all[all['COMPLETE_TIME']==4]\n",
    "m5 = all[all['COMPLETE_TIME']==5]\n",
    "m6 = all[all['COMPLETE_TIME']==6]\n",
    "m7 = all[all['COMPLETE_TIME']==7]\n",
    "#4月\n",
    "#按mob分类\n",
    "#3月完成\n",
    "list_m3_t_4 = list(m3[m3['STATUS4']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m3_m1_4 = list(m3[m3['REPAY_TIME4']>='2019-05-01']['ACCOUNT_ID'])+list(m3[m3['STATUS4']=='OPEN']['ACCOUNT_ID'])\n",
    "list_m3_c_4 = list(set(m3['ACCOUNT_ID']) ^ set(list_m3_t_4 + list_m3_m1_4))\n",
    "\n",
    "list_all_t_4 = list_m3_t_4\n",
    "list_all_m1_4 = list_m3_m1_4\n",
    "list_all_c_4 = list_m3_c_4 + list(m4['ACCOUNT_ID'])\n",
    "#5月\n",
    "#按mob分类\n",
    "#3月完成\n",
    "list_m3_t_5  = list(m3[m3['STATUS5']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m3_m1_5 = list(m3[(m3['STATUS5']=='OPEN') & (m3['STATUS4']!='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME5']>='2019-06-01') & (m3['REPAY_TIME4']<'2019-06-01')]['ACCOUNT_ID'])\n",
    "list_m3_m2_5 = list(m3[(m3['STATUS5']=='OPEN') & (m3['STATUS4']=='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME5']>='2019-06-01') & (m3['REPAY_TIME4']>='2019-06-01')]['ACCOUNT_ID'])\n",
    "list_m3_c_5  = list(set(m3['ACCOUNT_ID']) ^ set(list_m3_t_5 + list_m3_m1_5 + list_m3_m2_5))\n",
    "\n",
    "\n",
    "#4月完成\n",
    "list_m4_t_5 = list(m4[m4['STATUS5']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m4_m1_5 = list(m4[m4['STATUS5']=='OPEN']['ACCOUNT_ID']) + list(m4[m4['REPAY_TIME5']>='2019-06-01']['ACCOUNT_ID'])\n",
    "list_m4_c_5  = list(set(m4['ACCOUNT_ID']) ^ set(list_m4_t_5 + list_m4_m1_5))\n",
    "\n",
    "#总的\n",
    "list_all_t_5  = list_m3_t_5 + list_m4_t_5\n",
    "list_all_m1_5 = list_m3_m1_5 +list_m4_m1_5\n",
    "list_all_m2_5 = list_m3_m2_5\n",
    "list_all_c_5  = list_m3_c_5 + list_m4_c_5 + list(m5['ACCOUNT_ID'])\n",
    "\n",
    "#6月\n",
    "#按mob分类\n",
    "#3月完成\n",
    "list_m3_t_6  = list(m3[m3['STATUS6']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m3_m1_6 = list(m3[(m3['STATUS6']=='OPEN') & (m3['STATUS5']!='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME6']>='2019-07-01') & (m3['REPAY_TIME5']<'2019-07-01')]['ACCOUNT_ID'])\n",
    "list_m3_m2_6 = list(m3[(m3['STATUS6']=='OPEN') & (m3['STATUS5']=='OPEN') & (m3['STATUS4']!='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME6']>='2019-07-01') & (m3['REPAY_TIME5']>='2019-07-01') & (m3['REPAY_TIME4']<'2019-07-01')]['ACCOUNT_ID'])\n",
    "list_m3_m3_6 = list(m3[(m3['STATUS6']=='OPEN') & (m3['STATUS5']=='OPEN') & (m3['STATUS4']=='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME6']>='2019-07-01') & (m3['REPAY_TIME5']>='2019-07-01') & (m3['REPAY_TIME4']>='2019-07-01')]['ACCOUNT_ID'])\n",
    "list_m3_c_6  = list(set(m3['ACCOUNT_ID']) ^ set(list_m3_t_6 + list_m3_m1_6 + list_m3_m2_6 + list_m3_m3_6))\n",
    "\n",
    "#4月完成\n",
    "list_m4_t_6 = list(m4[m4['STATUS6']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m4_m1_6 = list(m4[(m4['STATUS6']=='OPEN') & (m4['STATUS5']!='OPEN')]['ACCOUNT_ID']) + list(m4[(m4['REPAY_TIME6']>='2019-07-01') & (m4['REPAY_TIME5']<'2019-07-01')]['ACCOUNT_ID'])\n",
    "list_m4_m2_6 = list(m4[(m4['STATUS6']=='OPEN') & (m4['STATUS5']=='OPEN')]['ACCOUNT_ID']) + list(m4[(m4['REPAY_TIME6']>='2019-07-01') & (m4['REPAY_TIME5']>='2019-07-01')]['ACCOUNT_ID'])\n",
    "list_m4_c_6  = list(set(m4['ACCOUNT_ID']) ^ set(list_m4_t_6 + list_m4_m1_6 + list_m4_m2_6))\n",
    "# 5月\n",
    "list_m5_t_6  = list(m5[m5['STATUS6']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m5_m1_6 = list(m5[m5['STATUS6']=='OPEN']['ACCOUNT_ID']) + list(m5[m5['REPAY_TIME6']>='2019-07-01']['ACCOUNT_ID'])\n",
    "list_m5_c_6  = list(set(m5['ACCOUNT_ID']) ^ set(list_m5_t_6 + list_m5_m1_6))\n",
    "\n",
    "#总的\n",
    "list_all_t_6  = list_m3_t_6 + list_m4_t_6 + list_m5_t_6\n",
    "list_all_m1_6 = list_m3_m1_6 + list_m4_m1_6 + list_m5_m1_6\n",
    "list_all_m2_6 = list_m3_m2_6 + list_m4_m2_6\n",
    "list_all_m3_6 = list_m3_m3_6\n",
    "list_all_c_6  = list_m3_c_6 + list_m4_c_6 + list_m5_c_6 + list(m6['ACCOUNT_ID'])\n",
    "len(list_all_c_6)\n",
    "\n",
    "#7月\n",
    "#按mob分类\n",
    "#3月完成\n",
    "list_m3_t_7  = list(m3[m3['STATUS7']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m3_m1_7 = list(m3[(m3['STATUS7']=='OPEN') & (m3['STATUS6']!='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME7']>='2019-08-01') & (m3['REPAY_TIME6']<'2019-08-01')]['ACCOUNT_ID'])\n",
    "list_m3_m2_7 = list(m3[(m3['STATUS7']=='OPEN') & (m3['STATUS6']=='OPEN') & (m3['STATUS5']!='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME7']>='2019-08-01') & (m3['REPAY_TIME6']>='2019-08-01') & (m3['REPAY_TIME5']<'2019-08-01')]['ACCOUNT_ID'])\n",
    "list_m3_m3_7 = list(m3[(m3['STATUS7']=='OPEN') & (m3['STATUS6']=='OPEN') & (m3['STATUS5']=='OPEN')&(m3['STATUS4']!='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME7']>='2019-08-01') & (m3['REPAY_TIME6']>='2019-08-01') & (m3['REPAY_TIME5']>='2019-08-01') & (m3['REPAY_TIME4']<'2019-08-01')]['ACCOUNT_ID'])\n",
    "list_m3_m4_7 = list(m3[(m3['STATUS7']=='OPEN') & (m3['STATUS6']=='OPEN') & (m3['STATUS5']=='OPEN')&(m3['STATUS4']=='OPEN')]['ACCOUNT_ID']) + list(m3[(m3['REPAY_TIME7']>='2019-08-01') & (m3['REPAY_TIME6']>='2019-08-01') & (m3['REPAY_TIME5']>='2019-08-01') & (m3['REPAY_TIME4']>='2019-08-01')]['ACCOUNT_ID'])\n",
    "list_m3_c_7  = list(set(m3['ACCOUNT_ID']) ^ set(list_m3_t_7 + list_m3_m1_7 + list_m3_m2_7 + list_m3_m3_7 + list_m3_m4_7))\n",
    "\n",
    "#4月完成\n",
    "list_m4_t_7  = list(m4[m4['STATUS7']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m4_m1_7 = list(m4[(m4['STATUS7']=='OPEN') & (m4['STATUS6']!='OPEN')]['ACCOUNT_ID']) + list(m4[(m4['REPAY_TIME7']>='2019-08-01') & (m4['REPAY_TIME6']<'2019-08-01')]['ACCOUNT_ID'])\n",
    "list_m4_m2_7 = list(m4[(m4['STATUS7']=='OPEN') & (m4['STATUS6']=='OPEN') & (m4['STATUS5']!='OPEN')]['ACCOUNT_ID']) + list(m4[(m4['REPAY_TIME7']>='2019-08-01') & (m4['REPAY_TIME6']>='2019-08-01') & (m4['REPAY_TIME5']<'2019-08-01')]['ACCOUNT_ID'])\n",
    "list_m4_m3_7 = list(m4[(m4['STATUS7']=='OPEN') & (m4['STATUS6']=='OPEN') & (m4['STATUS5']=='OPEN')]['ACCOUNT_ID']) + list(m4[(m4['REPAY_TIME7']>='2019-08-01') & (m4['REPAY_TIME6']>='2019-08-01') & (m4['REPAY_TIME5']>='2019-08-01')]['ACCOUNT_ID'])\n",
    "list_m4_c_7  = list(set(m4['ACCOUNT_ID']) ^ set(list_m4_t_7 + list_m4_m1_7 + list_m4_m2_7 + list_m4_m3_7)\n",
    "\n",
    "#5月完成\n",
    "list_m5_t_7  = list(m5[m5['STATUS7']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m5_m1_7 = list(m5[(m5['STATUS7']=='OPEN') & (m5['STATUS6']!='OPEN')]['ACCOUNT_ID']) + list(m5[(m5['REPAY_TIME7']>='2019-08-01') & (m5['REPAY_TIME6']<'2019-08-01')]['ACCOUNT_ID'])\n",
    "list_m5_m2_7 = list(m5[(m5['STATUS7']=='OPEN') & (m5['STATUS6']=='OPEN')]['ACCOUNT_ID']) + list(m5[(m5['REPAY_TIME7']>='2019-08-01') & (m5['REPAY_TIME6']>='2019-08-01')]['ACCOUNT_ID'])\n",
    "list_m5_c_7  = list(set(m5['ACCOUNT_ID']) ^ set(list_m5_t_7 + list_m5_m1_7 + list_m5_m2_7)\n",
    "\n",
    "#6月完成\n",
    "list_m6_t_7  = list(m6[m6['STATUS7']=='TERMINATE']['ACCOUNT_ID'])\n",
    "list_m6_m1_7 = list(m6[(m6['STATUS7']=='OPEN')]['ACCOUNT_ID']) + list(m6[(m6['REPAY_TIME7']>='2019-08-01')]['ACCOUNT_ID'])\n",
    "list_m6_c_7  = list(set(m6['ACCOUNT_ID']) ^ set(list_m6_t_7 + list_m6_m1_7)\n",
    "\n",
    "#总的\n",
    "list_all_t_7 = list_m3_t_7 + list_m4_t_7 + list_m5_t_7 + list_m6_t_7\n",
    "list_all_m1_7 = list_m3_m1_7 + list_m4_m1_7 + list_m5_m1_7 + list_m6_m1_7\n",
    "list_all_m2_7 = list_m3_m2_7 + list_m4_m2_7 + list_m5_m2_7\n",
    "list_all_m3_7 = list_m3_m3_7 + list_m4_m3_7\n",
    "list_all_m4_7 = list_m3_m4_7\n",
    "list_all_c_7  = list_m3_c_7 + list_m4_c_7 + list_m5_c_7 + list_m6_c_7 + list(m7['ACCOUNT_ID'])\n",
    "\n",
    "\n",
    "# 计算结果存到dataframe\n",
    "# C-M1\n",
    "c_m1_04_z = len(list_all_m1_4)\n",
    "c_m1_04_m = m3.shape[0]\n",
    "c_m1_05_z = len(list_all_m1_5)\n",
    "c_m1_05_m = len(list_all_c_4)\n",
    "c_m1_06_z = len(list_all_m1_6)\n",
    "c_m1_06_m = len(list_all_c_5)\n",
    "c_m1_07_z = len(list_all_m1_7)\n",
    "c_m1_07_m = len(list_all_c_6)\n",
    "\n",
    "\n",
    "# M1-M2\n",
    "m1_m2_05_z = len(list_all_m2_5)\n",
    "m1_m2_05_m = len(list_all_m1_4)\n",
    "m1_m2_06_z = len(list_all_m2_6)\n",
    "m1_m2_06_m = len(list_all_m1_5)\n",
    "m1_m2_07_z = len(list_all_m2_7)\n",
    "m1_m2_07_m = len(list_all_m1_6)\n",
    "\n",
    "# M2-M3\n",
    "m2_m3_06_z = len(list_all_m3_6)\n",
    "m2_m3_06_m = len(list_all_m2_5)\n",
    "m2_m3_07_z = len(list_all_m3_7)\n",
    "m2_m3_07_m = len(list_all_m2_6)\n",
    "\n",
    "# M3-M4\n",
    "m3_m4_07_z = len(list_all_m4_7)\n",
    "m3_m4_07_m = len(list_all_m3_6)\n",
    "                    \n",
    "                    \n",
    "# C-M2\n",
    "c_m2_05_z = c_m1_04_z * m1_m2_05_z\n",
    "c_m2_05_m = c_m1_04_m * m1_m2_05_m\n",
    "c_m2_06_z = c_m1_05_z * m1_m2_06_z\n",
    "c_m2_06_m = c_m1_05_m * m1_m2_06_m\n",
    "c_m2_07_z = c_m1_06_z * m1_m2_07_z\n",
    "c_m2_07_m = c_m1_06_m * m1_m2_07_m\n",
    "\n",
    "# C-M3\n",
    "c_m3_06_z = c_m1_04_z * m1_m2_05_z * m2_m3_06_z\n",
    "c_m3_06_m = c_m1_04_m * m1_m2_05_m * m2_m3_06_m\n",
    "c_m3_07_z = c_m1_05_z * m1_m2_06_z * m2_m3_07_z\n",
    "c_m3_07_m = c_m1_05_m * m1_m2_06_m * m2_m3_07_m\n",
    "\n",
    "# C-M4\n",
    "c_m4_07_z = c_m1_04_z * m1_m2_05_z * m2_m3_06_z * m3_m4_07_z\n",
    "c_m4_07_m = c_m1_04_m * m1_m2_05_m * m2_m3_06_m * m3_m4_07_m\n",
    "\n",
    "\n",
    "#C-C\n",
    "c_c_04_z = len(set(list_all_c_4)&set(m3['ACCOUNT_ID']))\n",
    "c_c_04_m = len(set(m3['ACCOUNT_ID']))\n",
    "c_c_05_z = len(set(list_all_c_4)&set(list_all_c_5))\n",
    "c_c_05_m = len(set(list_all_c_4))\n",
    "c_c_06_z = len(set(list_all_c_6)&set(list_all_c_5))\n",
    "c_c_06_m = len(set(list_all_c_5))\n",
    "c_c_07_z = len(set(list_all_c_7)&set(list_all_c_6))\n",
    "c_c_07_m = len(set(list_all_c_6))\n",
    "#M1-C\n",
    "m1_c1_05_z = len(set(list_all_m1_4)&set(list_all_c_5))\n",
    "m1_c1_05_m = len(set(list_all_m1_4))\n",
    "m1_c1_06_z = len(set(list_all_m1_5)&set(list_all_c_6))\n",
    "m1_c1_06_m = len(set(list_all_m1_5))\n",
    "m1_c1_07_z = len(set(list_all_m1_6)&set(list_all_c_7))\n",
    "m1_c1_07_m = len(set(list_all_m1_6))\n",
    "#M2-C\n",
    "m2_c_06_z = len(set(list_all_m2_5)&set(list_all_c_6))\n",
    "m2_c_06_m = len(list_all_m2_5)\n",
    "m2_c_07_z = len(set(list_all_m2_6)&set(list_all_c_7))\n",
    "m2_c_07_m = len(list_all_m2_6)\n",
    "# M3-C\n",
    "m3_c_07_z = len(set(list_all_m3_6)&set(list_all_c_7))\n",
    "m3_c_07_m = len(list_all_m3_6)\n",
    "#M1-M1\n",
    "m1_m1_05_z = len(set(list_all_m1_5)&set(list_all_m1_4))\n",
    "m1_m1_05_m = len(set(list_all_m1_4))\n",
    "m1_m1_06_z = len(set(list_all_m1_6)&set(list_all_m1_5))\n",
    "m1_m1_06_m = len(set(list_all_m1_5))\n",
    "m1_m1_07_z = len(set(list_all_m1_7)&set(list_all_m1_6))\n",
    "m1_m1_07_m = len(set(list_all_m1_6))\n",
    "\n",
    "#M2-M1\n",
    "m2_m1_06_z = len(set(list_all_m2_5)&set(list_all_m1_6))\n",
    "m2_m1_06_m = len(set(list_all_m2_5))\n",
    "m2_m1_07_z = len(set(list_all_m2_6)&set(list_all_m1_7))\n",
    "m2_m1_07_m = len(set(list_all_m2_6))\n",
    "\n",
    "# M3-M1\n",
    "m3_m1_07_z = len(set(list_all_m3_6)&set(list_all_m1_7))\n",
    "m3_m1_07_m = len(list_all_m3_6)\n",
    "\n",
    "                    \n",
    "# 导出数据\n",
    "date = {\n",
    "    \"billingmonth\":[\"C-M1\",\"M1-M2\",\"M2-M3\",\"C-C\",\"M1-C\",\"M2-C\",\"M1-M1\",\"M2-M1\"],\n",
    "    \"201904\"      :[c_m1_04,\"\",\"\",c_c_04,\"\",\"\",\"\",\"\"],\n",
    "    \"201905\"      :[c_m1_05,m1_m2_05,\"\",c_c_05,m1_c1_05,\"\",m1_m1_05,\"\"],\n",
    "    \"201906\"      :[c_m1_06,m1_m2_06,m2_m3_06,c_c_06,m1_c1_06,m2_c_06,m1_m1_06,m2_m1_06]\n",
    "}\n",
    "df=pd.DataFrame(date)\n",
    "df.to_csv('迁移率总体.csv')\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
